﻿//*******************************************************************/
//** 编 写 人：lardust-PC AFCodeAI
//** 编写日期：06/22/2013 04:47:04
//** 实现功能：
//** 修 改 人：
//** 修改日期：
//*******************************************************************/
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using CrazyTalk.Common;
using CrazyTalk.Data;
using CrazyTalk.IDAL;
using CrazyTalk.Models;

namespace CrazyTalk.MSSQLDAL
{
	public class TopicBaseDAL : ITopicBaseDAL
	{

		/// <summary>
		/// 添加
		/// </summary>
		/// <param name="model"></param>
		/// <returns></returns>
		public int Add(TopicBase model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append(@"insert into TopicBase(Title,Content,CreatorId,CreateDate,Editor,EditDate)
values (@Title,@Content,@CreatorId,@CreateDate,@Editor,@EditDate)
;select @@IDENTITY");
			IDataParameter[] parameters = 
			{
				new SqlParameter("@Title", SqlDbType.NVarChar,50){ Value = model.Title },
				new SqlParameter("@Content", SqlDbType.NText){ Value = model.Content },
				new SqlParameter("@CreatorId", SqlDbType.Int,4){ Value = model.CreatorId },
				new SqlParameter("@CreateDate", SqlDbType.DateTime){ Value = model.CreateDate },
				new SqlParameter("@Editor", SqlDbType.Int,4){ Value = model.Editor },
				new SqlParameter("@EditDate", SqlDbType.DateTime){ Value = model.EditDate }
			};

			return DbHelperSQL.GetInt(strSql.ToString(), parameters);
		}

		/// <summary>
		/// 删除
		/// </summary>
		/// <returns></returns>
		public bool Delete(int topicBaseId)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("delete from TopicBase ");
			strSql.Append(" where TopicBaseId=@TopicBaseId");
			IDataParameter[] parameters = 
			{
				new SqlParameter("@TopicBaseId", SqlDbType.Int,4){ Value = topicBaseId }
			};

			return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
		}

		/// <summary>
		/// 批量删除
		/// </summary>
		/// <param name="idList"></param>
		/// <returns></returns>
		public bool DeleteList(List<int> idList)
		{
			if (idList.Count == 0)
				return false;
			StringBuilder sbWhere = new StringBuilder();
			List<IDataParameter> lsPar = new List<IDataParameter>();
			for (int i = 0; i < idList.Count; i++)
			{
				string parameterName = "@Id" + i;
				sbWhere.AppendFormat("{0},", parameterName);
				lsPar.Add(new SqlParameter(parameterName, idList[i]));
			}
			sbWhere.Remove(sbWhere.Length - 1, 1);
			return DbHelperSQL.ExecuteSql(string.Format("delete from TopicBase where TopicBaseId in ({0})", sbWhere), lsPar.ToArray()) == idList.Count;
		}

		/// <summary>
		/// 更新
		/// </summary>
		/// <param name="model"></param>
		/// <returns></returns>
		public bool Update(TopicBase model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("update TopicBase set Title=@Title,Content=@Content,CreatorId=@CreatorId,CreateDate=@CreateDate,Editor=@Editor,EditDate=@EditDate");
			strSql.Append(" where  TopicBaseId=@TopicBaseId");
			IDataParameter[] parameters = 
			{
				new SqlParameter("@Title", SqlDbType.NVarChar,50){ Value = model.Title },
				new SqlParameter("@Content", SqlDbType.NText){ Value = model.Content },
				new SqlParameter("@CreatorId", SqlDbType.Int,4){ Value = model.CreatorId },
				new SqlParameter("@CreateDate", SqlDbType.DateTime){ Value = model.CreateDate },
				new SqlParameter("@Editor", SqlDbType.Int,4){ Value = model.Editor },
				new SqlParameter("@EditDate", SqlDbType.DateTime){ Value = model.EditDate }
			};

			return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
		}

		/// <summary>
		/// 是否存在行
		/// </summary>
		/// <returns></returns>
		public bool Exists(int topicBaseId)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select count(1) from TopicBase where TopicBaseId=@TopicBaseId ");
			IDataParameter[] parameters = 
			{
				new SqlParameter("@TopicBaseId", SqlDbType.Int,4){ Value = topicBaseId }
			};

			return DbHelperSQL.Exists(strSql.ToString(), parameters);
		}

		/// <summary>
		/// 获取数据实体
		/// </summary>
		/// <returns></returns>
		public TopicBase GetModel(int topicBaseId)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select * from TopicBase where TopicBaseId=@TopicBaseId");
			IDataParameter[] parameters = 
			{
				new SqlParameter("@TopicBaseId", SqlDbType.Int,4){ Value = topicBaseId }
			};

			DataTable dtb = DbHelperSQL.GetDataTable(strSql.ToString(), parameters);
			if (dtb.Rows.Count > 0)
			{
				return CreateModel(dtb.Rows[0]);
			}
			return null;
		}

		/// <summary>
		/// 使用数据行创建数据实体
		/// </summary>
		/// <param name="dr"></param>
		/// <returns></returns>
		private TopicBase CreateModel(DataRow dr)
		{
			TopicBase model = new TopicBase();
			model.TopicBaseId = ConvertUtil.ToInt(dr["TopicBaseId"]);
			model.Title = dr["Title"].ToString();
			model.Content = dr["Content"].ToString();
			model.CreatorId = ConvertUtil.ToInt(dr["CreatorId"]);
			model.CreateDate = ConvertUtil.ToDateTime(dr["CreateDate"]);
			model.Editor = ConvertUtil.ToInt(dr["Editor"]);
			model.EditDate = ConvertUtil.ToDateTime(dr["EditDate"]);

			return model;
		}

		/// <summary>
		/// 获取记录详情
		/// </summary>
		/// <returns></returns>
		public DataTable GetDetails(int topicBaseId)
		{
			IDataParameter[] parameters = 
			{
				new SqlParameter("@TopicBaseId", SqlDbType.Int,4){ Value = topicBaseId }
			};

			return DbHelperSQL.GetDataTable(@"select * from TopicBase where TopicBaseId=@TopicBaseId", parameters);
		}

		/// <summary>
		/// 分页查询
		/// </summary>
		/// <param name="queryField">查询条件的字段名称集合，字段格式：a.FieldName,>,参数名附加字符</param>
		/// <param name="queryValue">查询条件的值集合</param>
		/// <param name="orderby">排序列名</param>
		/// <param name="index">页码，从1开始</param>
		/// <param name="size">页记录数</param>
		/// <param name="totalCount">输出的总记录行数</param>
		/// <returns></returns>
		public DataTable GetList(List<string> queryField, List<object> queryValue, string orderby, int index, int size, out int totalCount)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("SELECT TT.* FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby);
			}
			else
			{
				strSql.Append("order by T.TopicBaseId");
			}
			strSql.Append(")AS Row, T.*  from TopicBase T where 1=1");
			StringBuilder sbWhere = new StringBuilder();
			List<IDataParameter> parm = new List<IDataParameter>();
			if (queryField != null && queryField.Count > 0)
			{
				for (int i = 0; i < queryField.Count; i++)
				{
					string qualifier = " = ";
					string parameterAddin = "";
					//最后次变量用于存储 判断限定符 左边 的 输入 如 a.FieldName
					string fieldNameIncom = queryField[i];
					string[] curArg = fieldNameIncom.Split(',');
					if (curArg.Length > 1)
					{
						fieldNameIncom = curArg[0];
						qualifier = curArg[1];
					}
					if (curArg.Length > 2)
					{
						parameterAddin = curArg[2];
					}
					string fieldName = fieldNameIncom;
					int ditIndex = fieldName.IndexOf('.');
					if (ditIndex > 0)
					{
						fieldName = fieldName.Substring(ditIndex + 1);
					}
					sbWhere.AppendFormat(" AND {0}{1}@{2}{3} ", fieldNameIncom, qualifier, fieldName, parameterAddin);
					parm.Add(new SqlParameter("@" + fieldName + parameterAddin, queryValue[i]));
				}
			}
			totalCount = DbHelperSQL.GetInt(@"select count(*) from TopicBase as a where 1=1" + sbWhere, parm.ToArray());

			strSql.Append(sbWhere);
			strSql.Append(" ) as TT ");
			int pageIndex = (index - 1) * size;
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", pageIndex, pageIndex + size);

			return DbHelperSQL.GetDataTable(strSql.ToString(), parm.ToArray());
		}
		/// <summary>
		/// 返回首页列表数据
		/// </summary>
		/// <param name="index">页码，从1开始</param>
		/// <param name="size">页记录数</param>
		/// <param name="totalCount">输出的总记录行数</param>
		/// <returns></returns>
		public DataTable GetIndexList(int index, int size, out int totalCount)
		{
			int pageIndex = (index - 1) * size;
			var lsParameter = new List<SqlParameter>();
			lsParameter.Add(new SqlParameter("@StartIndex", SqlDbType.Int) { Value = pageIndex });
			lsParameter.Add(new SqlParameter("@EndIndex", SqlDbType.Int) { Value = pageIndex + size });
			lsParameter.Add(new SqlParameter("@TotalCount", SqlDbType.Int) { Direction = ParameterDirection.Output });
			DataTable dtb = DbHelperSQL.GetDataTableByPro("p_get_Topic_Index"
				, lsParameter.ToArray()
				);
			totalCount = (int)lsParameter[2].Value;
			return dtb;
		}
	}
}